import pandas as pd
import numpy as np

df = pd.read_csv('../transcribed/nm_rla.csv')

df['state'] = "NEW MEXICO"
df['county'] = df['county'].str.upper()
df['date'] = '2020-11-03'
        
df['office'] = np.where((df['office'] == "Senator"), "US SENATE", df['office'])
df['office'] = np.where((df['office'] == "Representative"), "US REPRESENTATIVE", df['office'])
df['office'] = np.where((df['office'] == "Court"), "US COURT OF APPEALS JUDGE", df['office'])
df['office'] = np.where((df['office'] == "President"), "US PRESIDENT", df['office'])
        
#add in the candidates and upper case this and office
df['candidate'] = df['candidate'].str.upper()
df['office'] = df['office'].str.upper()

#standardize original votes and add in audited and diff
df['original_votes'] = df['original']
df['audited_votes'] = df['audited']

df = df[(df.original_votes != "-") & (df.audited_votes != "-")]

df["audited_votes"] = pd.to_numeric(df["audited_votes"])
df["original_votes"] = pd.to_numeric(df["original_votes"])

df['diff'] = df['audited_votes'] - df['original_votes']
           

df = df.drop(columns = ['type','original','audited'])

df = df[['state', 'county', 'date', 'office', 'precinct', 'candidate','original_votes', 'audited_votes','diff']]

df.to_csv('../ready/NM Recleaned.csv')
